<?php

namespace app\common\model;


use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use think\Model;
use think\model\concern\SoftDelete;

class Disease extends Model
{

    use SoftDelete;

    protected function type()
    {
        return $this->hasOne(Type::class, 'id', 'type_id');
    }

    protected $columns = ['A', 'B', 'C', 'D', 'E', 'F', 'G'];
    protected $headers = [
        ['序号', '桥幅', '构件部位及编号', '损害类型', '外观检测描述', '照片', '备注'],
        ['序号', '桥幅', '检测部位及位置', '设计值(mm)', '实测保护层厚度（mm）', '照片', '备注']
    ];

    public function createXlsx($project_id = 2)
    {
        $project = Project::where('id', $project_id)->find();
        if (!$project) throw new \Exception("请先添加项目信息");

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        // 设置全局居中
        $sheet->setTitle('查看报告')->getStyle('A1:G1000')->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER)
            ->setVertical(Alignment::VERTICAL_CENTER);;

        foreach ($this->columns as $column) {
            $sheet->getColumnDimension($column)->setWidth(30);
        }

        // 设置基本信息
        $this->setCellValueWithStyle($sheet, 'A1', '工程名称：', DataType::TYPE_STRING);
        $this->setCellValueWithStyle($sheet, 'B1', $project['title'], DataType::TYPE_STRING);

        $this->setCellValueWithStyle($sheet, 'A3', '开始时间：', DataType::TYPE_STRING);
        $this->setCellValueWithStyle($sheet, 'B3', $project['start_date'], DataType::TYPE_STRING);

        $this->setCellValueWithStyle($sheet, 'D3', '结束时间：', DataType::TYPE_STRING);
        $this->setCellValueWithStyle($sheet, 'E3', $project['end_date'], DataType::TYPE_STRING);

        $this->setCellValueWithStyle($sheet, 'A4', '桥梁长度：', DataType::TYPE_STRING);
        $this->setCellValueWithStyle($sheet, 'B4', $project['length'], DataType::TYPE_STRING);

        $this->setCellValueWithStyle($sheet, 'D4', '结构形式：', DataType::TYPE_STRING);
        $this->setCellValueWithStyle($sheet, 'E4', $project['structure'], DataType::TYPE_STRING);


        // 表头
        $start = 5;


        $diseases = Disease::where('project_id', $project_id)->with('type')->select();

        $diseaseList = [];
        foreach ($diseases as $item) {
            $diseaseList[$item['type']['title']][] = $item;
        }
        //外观检测
        $this->setTableDiseaseList($sheet, '外观检测', 0, $diseaseList, $start);
        //混凝土强度
        $this->setTableConcreteList($sheet, '混凝土强度', $diseaseList, $start);
        // 钢筋保护层厚度
        $start++;
        $this->setTableDiseaseList($sheet, '钢筋保护层厚度', 1, $diseaseList, $start);
        // 主拱圈线形
        $this->setTableConcreteList($sheet, '主拱圈线形', $diseaseList, $start);

        //剩下的全是文件
        foreach ($diseaseList as $name => $item) {
            $this->setTableConcreteList($sheet, $name, $diseaseList, $start);
        }

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $fileName = '/uploads/report/' . $project['title'] . '.xlsx';
        $writer->save('.'.$fileName);
        return $fileName;
    }

    public function setTableDiseaseList($sheet, $name, $headerRow, &$diseaseList, &$start)
    {
        $start += 2;
        $this->setCellValueWithStyle($sheet, 'A' . $start, $name, DataType::TYPE_STRING);
        $sheet->mergeCells('A' . $start . ':G' . $start);
        $this->setBoldFontSize($sheet, 'A' . $start . ':G' . $start, 16);
        $start++;
        foreach ($this->headers[$headerRow] as $index => $header) {
            $col = $this->columns[$index];
            $this->setCellValueWithStyle($sheet, $col . $start, $header, DataType::TYPE_STRING);
        }
        if (isset($diseaseList[$name])) {
            $startBin = $start - 1;
            foreach ($diseaseList[$name] as $key => $item) {
                $start++;
                $this->setCellValueWithStyle($sheet, 'A' . $start, $key + 1, DataType::TYPE_STRING);
                $this->setCellValueWithStyle($sheet, 'B' . $start, $item['title'], DataType::TYPE_STRING);
                $this->setCellValueWithStyle($sheet, 'C' . $start, $item['num'], DataType::TYPE_STRING);
                $this->setCellValueWithStyle($sheet, 'D' . $start, $item['pattern'], DataType::TYPE_STRING);
                $this->setCellValueWithStyle($sheet, 'E' . $start, $item['desc'], DataType::TYPE_STRING);

                // 插入图片
                $imagePath = str_replace(request()->domain(), './', $item['image']);
                if (file_exists($imagePath)) {
                    $drawing = new Drawing();
                    $drawing->setPath($imagePath);
                    $drawing->setCoordinates('F' . $start);
                    $drawing->setWidthAndHeight(100, 100);
                    $drawing->setWorksheet($sheet);
                }

                $sheet->getRowDimension($start)->setRowHeight(50);
                $this->setCellValueWithStyle($sheet, 'G' . $start, $item['memo'], DataType::TYPE_STRING);
            }
            $this->applyBorder($sheet, "A{$startBin}:G{$start}");
            unset($diseaseList[$name]);
        }
    }

    public function setTableConcreteList($sheet, $name, &$concreteList, &$start)
    {
        if (isset($concreteList[$name])) {
  
            foreach ($concreteList[$name] as $item) {
                $file = str_replace(request()->domain(), './', $item['file']);
                if (!file_exists($file)){
                    continue;
                } 
                $start+=3;
                $startHun = $start;
                $sheet->mergeCells("A{$start}:G{$start}");
                $this->setCellValueWithStyle($sheet, "A{$start}", $name, DataType::TYPE_STRING);
                $this->setBoldFontSize($sheet, "A{$start}:G{$start}", 16);
                $rowKey = ['A' => 'A', 'B' => 'B', 'C' => 'C', 'D' => 'D', 'E' => 'E','F'=>'F'];
                $listXlsx = getExcelFileData($file, $rowKey);
                
                foreach ($listXlsx as $startIndex=>$value) {
                    $start++;
                    $this->setCellValueWithStyle($sheet, "A{$start}", $startIndex+1, DataType::TYPE_STRING);
                    $this->setCellValueWithStyle($sheet, "B{$start}", $value['A'], DataType::TYPE_STRING);
                    $this->setCellValueWithStyle($sheet, "C{$start}", $value['B'], DataType::TYPE_STRING);
                    $this->setCellValueWithStyle($sheet, "D{$start}", $value['C'], DataType::TYPE_STRING);
                    $this->setCellValueWithStyle($sheet, "E{$start}", $value['D'], DataType::TYPE_STRING);
                    $this->setCellValueWithStyle($sheet, "F{$start}", $value['E'], DataType::TYPE_STRING);
                    $this->setCellValueWithStyle($sheet, "G{$start}", $value['F'], DataType::TYPE_STRING);
                }
                $this->applyBorder($sheet, "A{$startHun}:G{$start}");
                
            }
            unset($concreteList[$name]);
        }
    }

// 工具方法：设置单元格值并指定类型
    private function setCellValueWithStyle($sheet, $cell, $value, $type)
    {
        $sheet->setCellValueExplicit($cell, $value, $type);
    }

// 设置加粗和字体大小
    private function setBoldFontSize($sheet, $range, $size)
    {
        $sheet->getStyle($range)->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => $size,
            ]
        ]);
    }

// 应用边框样式
    private function applyBorder($sheet, $range)
    {
        $sheet->getStyle($range)->applyFromArray([
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '#000000'],
                ],
            ],
        ]);
    }

}